/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package mnstudent.dataacesslayer;

import java.sql.*;

/**
 *
 * @author lee
 */
public class DataExecuteUseStoreProcedure {

    //<editor-fold defaultstate="collapsed" desc="Attributes">
    private String m_sqlCmd;
    private CallableStatement m_callStatement;
    private DataConnection m_dtConnect;

    //</editor-fold>
    //Use with StoreProcedure
    //<editor-fold defaultstate="collapsed" desc="Constructor">
    public DataExecuteUseStoreProcedure() {
        this.m_dtConnect = new DataConnection();
        this.m_callStatement = null;
        this.m_sqlCmd = "";
    }

    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Method">
    /**
     * Get all data of table from database
     *
     * @param _sqlCmd
     * @return
     */
    public ResultSet getDataFromDatabase(String _sqlCmd, Object[] _arrValue) {
        this.m_sqlCmd = _sqlCmd;
        if (this.m_dtConnect.openConection()) {
            try {
                this.m_callStatement = this.m_dtConnect.getConnection().prepareCall(m_sqlCmd);
                if (_arrValue != null) {
                    for (int i = 0; i < _arrValue.length; i++) {
                        this.m_callStatement.setObject(i + 1, _arrValue[i]);
                    }
                }
//                CachedRowSetImpl crs = new CachedRowSetImpl();
//                crs.populate(this.m_callStatement.executeQuery());
//                return crs;
                return this.m_callStatement.executeQuery();
            } catch (SQLException ex) {
                this.m_dtConnect.closeConnection();
                return null;
            }
//            } finally {
//                this.m_dtConnect.closeConnection();
//            }
        } else {
            return null;
        }
    }

    public int updateDataToDatabase(String _sqlCmd, Object[] _arrValue) {
        this.m_sqlCmd = _sqlCmd;
        if (this.m_dtConnect.openConection()) {
            int _rowsAffected = 0;

            try {
                this.m_dtConnect.getConnection().setAutoCommit(true);
                this.m_callStatement = this.m_dtConnect.getConnection().prepareCall(_sqlCmd);

                if (_arrValue != null) {
                    for (int i = 0; i < _arrValue.length; i++) {
                        this.m_callStatement.setObject(i + 1, _arrValue[i]);
                    }
                }

                _rowsAffected = this.m_callStatement.executeUpdate();
                this.m_dtConnect.getConnection().commit();
            } catch (SQLException ex) {
                ex.printStackTrace();
                this.m_dtConnect.getConnection().rollback();
                _rowsAffected = -1;
            } finally {
                this.m_dtConnect.closeConnection();
                return _rowsAffected;
            }
        } else {
            return -1;
        }
    }

    public int[] updateMoreDataToDatabase(String SqlCmd, int _countParameter, Object[] _arrValue) {
        this.m_sqlCmd = SqlCmd;
        if (this.m_dtConnect.openConection()) {

            int[] updateCounts = null;
            try {
                this.m_dtConnect.getConnection().setAutoCommit(true);
                this.m_callStatement = m_dtConnect.getConnection().prepareCall(SqlCmd);
                int _index = 0;
                for (int i = 0; i < (_arrValue.length / _countParameter); ++i) {

                    for (int j = 1; j <= _countParameter; ++j) {
                        this.m_callStatement.setObject(j + 1, _arrValue[_index]);
                        ++_index;
                    }
                    this.m_callStatement.addBatch();

                }
                updateCounts = this.m_callStatement.executeBatch();
                this.m_dtConnect.getConnection().commit();
            } catch (SQLException ex) {
                //Don't execute
                this.m_dtConnect.getConnection().rollback();
                updateCounts = null;
            } finally {
                this.m_dtConnect.closeConnection();
                return updateCounts;
            }
        } else {
            return new int[]{'\0'};
        }
    }

    public boolean updateMoreTableToDatabase(CallableStatement[] _arrCallStatement) {
        if (this.m_dtConnect.openConection()) {
            boolean _result = false;
            try {
                this.m_dtConnect.getConnection().setAutoCommit(true);
                try {
                    for (CallableStatement item : _arrCallStatement) {
                        item.executeUpdate();
                    }
                    _result = true;
                } catch (SQLException ex) {
                    for (CallableStatement item : _arrCallStatement) {
                        item.close();
                    }
                }
                this.m_dtConnect.getConnection().commit();
            } catch (SQLException ex) {
                try {
                    this.m_dtConnect.getConnection().rollback();
                } catch (SQLException ex1) {
                }
            } finally {
                this.m_dtConnect.closeConnection();
                return _result;
            }
        } else {
            return false;
        }
    }
    //</editor-fold>
}
